package com.xvaca.spcms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.xvaca.spcms.bean.PartnerBean;
import com.xvaca.spcms.db.DBConnector;

public class PartnerDAO {
	
	private static final Logger logger = Logger.getLogger(PartnerDAO.class);
	
	private static String INSERT_PARTNER_SQL = "INSERT INTO partners (name, contact, cycle, account, status, created_at, updated_at) " +
			"VALUES (?, ?, ?, ?, ?, ?, ?)";
	
	private static String UPDATE_PARTNER_SQL = "UPDATE partners set name = ?, contact = ?, cycle = ?, account = ?, status = ?, updated_at = ? WHERE id = ?";
	
	private static String SELECT_PARTNER_SQL = "SELECT * FROM partners order by id";
	
	private static String SELECT_PARTNER_BY_ID_SQL = "SELECT * FROM partners where id = ?";
	
	public void insertPartner(PartnerBean partner) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(INSERT_PARTNER_SQL);
			pstmt.setString(1, partner.getName());
			pstmt.setString(2, partner.getContact());
			pstmt.setInt(3, partner.getCycle());
			pstmt.setString(4, partner.getAccount());
			pstmt.setInt(5, partner.getStatus());
			pstmt.setString(6, partner.getCreatedAt());
			pstmt.setString(7, partner.getUpdatedAt());
			
			pstmt.execute();
			
		} catch (Exception e) {
			logger.error("插入合作方信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, null);
		}
	}
	
	public void updatePartner(PartnerBean partner) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(UPDATE_PARTNER_SQL);
			pstmt.setString(1, partner.getName());
			pstmt.setString(2, partner.getContact());
			pstmt.setInt(3, partner.getCycle());
			pstmt.setString(4, partner.getAccount());
			pstmt.setInt(5, partner.getStatus());
			pstmt.setString(6, partner.getUpdatedAt());
			pstmt.setInt(7, partner.getId());
			
			pstmt.execute();
			
		} catch (Exception e) {
			logger.error("更新合作方信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, null);
		}
	}
	
	public List<PartnerBean> getPartnerList() {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<PartnerBean> list = new ArrayList<PartnerBean>();
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(SELECT_PARTNER_SQL);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				PartnerBean partner = new PartnerBean();
				partner.setAccount(rs.getString("account"));
				partner.setContact(rs.getString("contact"));
				partner.setCreatedAt(rs.getString("created_at"));
				partner.setCycle(rs.getInt("cycle"));
				partner.setId(rs.getInt("id"));
				partner.setName(rs.getString("name"));
				partner.setStatus(rs.getInt("status"));
				partner.setUpdatedAt(rs.getString("updated_at"));
				
				list.add(partner);
			}
		} catch (Exception e) {
			logger.error("查询合作方列表信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return list;
		
	}
	
	/**
	 * 根据ID获取合作方信息
	 * @param id
	 * @return
	 */
	public PartnerBean getPartnerById(int id) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		PartnerBean partner = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(SELECT_PARTNER_BY_ID_SQL);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				partner = new PartnerBean();
				partner.setAccount(rs.getString("account"));
				partner.setContact(rs.getString("contact"));
				partner.setCreatedAt(rs.getString("created_at"));
				partner.setCycle(rs.getInt("cycle"));
				partner.setId(rs.getInt("id"));
				partner.setName(rs.getString("name"));
				partner.setStatus(rs.getInt("status"));
				partner.setUpdatedAt(rs.getString("updated_at"));
			}
		} catch (Exception e) {
			logger.error("查询合作方列表信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return partner;
		
	}
}
